package com.ebt.m.data;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.net.Uri;
import android.provider.ContactsContract;
import android.text.TextUtils;

import com.ebt.m.AppContext;
import com.ebt.m.R;
import com.ebt.m.customer.constant.CustomerConstant;
import com.ebt.m.customer.db.CustomerDBManager;
import com.ebt.m.customer.db.CustomerInteraction;
import com.ebt.m.customer.db.InteractionUtil;
import com.ebt.m.customer.entity.CustomerDetail;
import com.ebt.m.customer.entity.CustomerSimple;
import com.ebt.m.customer.event.EventRefreshBadge;
import com.ebt.m.customer.item.Contact;
import com.ebt.m.customer.model.CustomerFromContact;
import com.ebt.m.customer.model.CustomerModelDetail;
import com.ebt.m.customer.util.CompareHelper;
import com.ebt.m.customer.util.CustomerConvertUtil;
import com.ebt.m.data.bean.UserInfo;
import com.ebt.m.data.entity.ACustomerInfoItemContent;
import com.ebt.m.data.entity.Customer;
import com.ebt.m.data.entity.CustomerInfoItemContactContent;
import com.ebt.m.data.entity.CustomerInfoItemEmailContent;
import com.ebt.m.data.entity.CustomerInfoItemIMContent;
import com.ebt.m.data.entity.DBConstant;
import com.ebt.m.data.entity.EntityCustomerAddress;
import com.ebt.m.data.entity.EntityCustomerAttachment;
import com.ebt.m.data.entity.EntityCustomerCGroup;
import com.ebt.m.data.entity.EntityCustomerGroups;
import com.ebt.m.data.entity.EntityCustomerNote;
import com.ebt.m.data.greendao.CustomerInteractionDao;
import com.ebt.m.proposal_v2.utils.EnumProfession;
import com.ebt.m.utils.DateUtils;

import org.greenrobot.eventbus.EventBus;
import org.greenrobot.greendao.query.QueryBuilder;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.UUID;


public class CustomerData {

    private static final String TAG = CustomerData.class.getSimpleName();

    private DatabaseManager dm;
    public static final int ORDER_BY_PREFIX = 0;
    public static final int ORDER_BY_AGE = 1;
    public static final int ORDER_BY_LEVEL = 2;
    public static final int ORDER_BY_DEMO = 3;
    public static final int ORDER_BY_REGULAR = 4;
    public static final int ORDER_BY_SEX = 5;
    public static final int ORDER_ASC = 0;// 升序
    public static final int ORDER_DESC = 1;// 降序

    public static final int WAIT_NEW = 100;// 待新增
    public static final int WAIT_DELETE = 110;// 待删除
    public static final int WAIT_UPDATE = 120;// 待更新

    private Context mContext;

    public CustomerData(Context context) {
        dm = DatabaseManager.getInstance(context);
        mContext = context;
    }

    /**
     * 向表中保存对象
     */
    public long insertObject(String tableName, Object o) {
        long rowId = 0;
        if (o != null) {
            try {
                dm.open();
                rowId = dm.insert(tableName, null, o);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (dm != null) {
                    dm.close();
                }
            }
        }
        return rowId;
    }

    public boolean clearTable(String tableName) {
        if (TextUtils.isEmpty(tableName)) {
            return false;
        }

        String sql_0 = "delete from '" + tableName + "';";
        String sql_1 = "update sqlite_sequence set seq=0 where name='" + tableName + "';";

        try {
            dm.open();
            dm.executeSql(sql_0);
            dm.executeSql(sql_1);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return true;
    }

    /**
     * 更新返回ID
     *
     * @return
     */
    public int updateObjectWithChangeStatus(String tableName, Object o, String uuid) {
        int ret = 0;
        if (o == null) {
            return ret;
        }

        try {
            dm.open();
            ret = dm.updateWithChangeStatus(tableName, new String[]{"uuid"}, new String[]{uuid}, o);
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    public boolean updateObjectWithoutChangeStatus(String tableName, Object o, String uuid) {
        boolean ret = false;
        if (o == null) {
            return ret;
        }

        try {
            dm.open();
            ret = dm.update(tableName, new String[]{"uuid"}, new String[]{uuid}, o);
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 更新返回ID
     *
     * @return
     */
    public int insertOrUpdateObjectWithChangeStatus(String tableName, Object o, String uuid) {
        int ret = 0;
        if (o == null) {
            return ret;
        }

        try {
            dm.open();
            Cursor c = dm.query(tableName, new String[]{"uuid"}, "uuid=?", new String[]{uuid}, null);
            if (c != null && c.getCount() > 0) {
                ret = dm.updateWithChangeStatus(tableName, new String[]{"uuid"}, new String[]{uuid}, o);
                c.close();
                c = null;
            } else {
                ret = (int) dm.insertWithChangeStatus(tableName, null, o);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 删除tableName表中的一条信息
     */
    public long pretendDeleteAnObject(String tableName, String uuid) {
        long ret = 0;
        try {
            dm.open();
            ret += softDelete(tableName, uuid);
            ret += hardDelete(tableName, uuid);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 删除tableName表中的多条信息
     */
    public long pretendDeleteObjects(String tableName, List<String> uuids) {
        long ret = 0;
        try {
            dm.open();

            dm.beginTransaction();
            for (String uuid : uuids) {
                ret += softDelete(tableName, uuid);
                ret += hardDelete(tableName, uuid);
            }
            dm.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.endTransaction();
                dm.close();
            }
        }
        return ret;
    }

    //如果使用Contact，那么需要从contact中解析电话号码，需要同时解析电话号码
    public List<Contact> getListOfContactForCustomerGet() {
        List<CustomerFromContact> listIgnores = null;
        List<Contact> listContacts = null;
        List<Contact> listContactsServivors = null;
        Cursor cursor = null;
        try {
            dm.open();
            listContacts = getListOfContact(CustomerConstant.CustomerGet.GET_CONTACT_MAX);

            if (listContacts == null || listContacts.size() == 0) {
                return listContacts;
            }

            String sqlGetIgnores = "select * from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY)
                    + " ORDER BY " + CustomerConstant.CustomerFromContact.CONTACT_SORT_KEY + " ASC;";
            listIgnores = dm.sql2VOList(sqlGetIgnores, new CustomerFromContact());
            //进行比较删除
            Collections.sort(listContacts, CompareHelper.COMPARATOR_CONTACT);
            if (listIgnores == null || listIgnores.size() == 0) {
                return listContacts;
            } else {
                Collections.sort(listIgnores, CompareHelper.COMPARATOR_CUSTOMER_FROM_CONTACT);
                listIgnores = CompareHelper.removeSameCustomerFromContact(listIgnores);
                listContactsServivors = CompareHelper.setIgnoreMarksOfContactFromCustomerContact(listContacts, listIgnores, true, false);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        if (listContactsServivors != null && listContactsServivors.size() > 0) {
            //获取已经存在的客户名字
//            Set<String> passedNames = getPassedName();
            List<String> passedNames = CustomerDBManager
                    .getInstance()
                    .getSimpleCustomerNames(mContext);

            for(Contact item : listContactsServivors){
                if (item.hasImported){
                    if (!passedNames.contains(item.getName())){
                        item.hasImported = false;
                    }
                }
            }
        }
        return listContactsServivors;
    }

    //如果使用Contact，那么需要从contact中解析电话号码，需要同时解析电话号码
    public long getNumberOfContactForCustomerGet() {

        List<CustomerFromContact> listIgnores = null;
        List<Contact> listContacts = null;
        List<Contact> listContactsServivors = null;
        Cursor cursor = null;
        try {
            dm.open();
            listContacts = getListOfContact(CustomerConstant.CustomerGet.GET_CONTACT_MAX);

            if (listContacts == null || listContacts.size() == 0) {
                return 0;
            }

            String sqlGetIgnores = "select * from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY)
                    + " ORDER BY " + CustomerConstant.CustomerFromContact.CONTACT_SORT_KEY + " ASC;";
            listIgnores = dm.sql2VOList(sqlGetIgnores, new CustomerFromContact());
            //进行比较删除
            Collections.sort(listContacts, CompareHelper.COMPARATOR_CONTACT);
            if (listIgnores == null || listIgnores.size() == 0) {
                return listContacts.size();
            } else {
                Collections.sort(listIgnores, CompareHelper.COMPARATOR_CUSTOMER_FROM_CONTACT);
                listIgnores = CompareHelper.removeSameCustomerFromContact(listIgnores);
                listContactsServivors = CompareHelper.setIgnoreMarksOfContactFromCustomerContact(listContacts, listIgnores, false, false);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        return listContactsServivors.size();
    }

    //如果使用Contact，那么需要从contact中解析电话号码，需要同时解析电话号码
    public long getNumberOfContactForCustomerGetNew() {
        List<CustomerFromContact> listIgnores;
        List<Contact> listContacts;
        List<Contact> listContactsServivors = null;
        Cursor cursor = null;
        try {
            dm.open();
            listContacts = getListOfContact(CustomerConstant.CustomerGet.GET_CONTACT_MAX);

            if (listContacts == null || listContacts.size() == 0) {
                return 0;
            }

            String sqlGetIgnores = "select * from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY)
                    + " ORDER BY " + CustomerConstant.CustomerFromContact.CONTACT_SORT_KEY + " ASC;";
            listIgnores = dm.sql2VOList(sqlGetIgnores, new CustomerFromContact());
            //进行比较删除
            Collections.sort(listContacts, CompareHelper.COMPARATOR_CONTACT);
            if (listIgnores == null || listIgnores.size() == 0) {
                listContactsServivors = listContacts;
            } else {
                Collections.sort(listIgnores, CompareHelper.COMPARATOR_CUSTOMER_FROM_CONTACT);
                listIgnores = CompareHelper.removeSameCustomerFromContact(listIgnores);
                listContactsServivors = CompareHelper.setIgnoreMarksOfContactFromCustomerContact(listContacts, listIgnores, false, false);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        return getListContactsExceptPassed(listContactsServivors).size();
    }

    private List<Contact> getListContactsExceptPassed(List<Contact> listContactsServivors) {
        List<Contact> list = new ArrayList<>();
        if (listContactsServivors == null) {
            return list;
        }
        Set<String> set = getPassedName();
        if (set == null) {
            return listContactsServivors;
        }
        for (Contact contact : listContactsServivors) {
            if (!TextUtils.isEmpty(contact.name)) {
                if (set.contains(contact.name)) {
                    continue;
                } else {
                    list.add(contact);
                }
            }
        }
        return list;
    }

    private Set<String> getPassedName() {
        Set<String> set;
        SharedPreferences preferences = mContext.getSharedPreferences("passed_contact", Context.MODE_PRIVATE);
        set = preferences.getStringSet("set", null);
        return set;
    }

    public void setPassedName() {
        Set<String> set = new HashSet<>();

        List<Contact> list = getListOfContact(CustomerConstant.CustomerGet.GET_CONTACT_MAX);
        if (list != null && list.size() > 0) {
            for (Contact contact : list) {
                if (contact != null && !TextUtils.isEmpty(contact.name)) {
                    set.add(contact.name);
                }
            }
            setPassedName(set);
        }
    }

    private void setPassedName(Set<String> set) {
        SharedPreferences preferences = mContext.getSharedPreferences("passed_contact", Context.MODE_PRIVATE);
        SharedPreferences.Editor editor = preferences.edit();
        editor.putStringSet("set", set);
        editor.commit();
        EventBus.getDefault().post(new EventRefreshBadge());
    }

    /**
     * huoke用于获客模块的数据导入，如果是“导入”，则是isImport是true；如果是忽略，则是false
     * 返回值说明：
     * 1.如果是import，那么返回的uuid是customerUuid
     * 2.如果是ignore，那么返回的uuid是customerFromContact的uuid
     *
     * @param o
     * @param isImport
     * @return
     */
    public String insertCustomerFromContact(Object o, boolean isImport) {
        //isImport是互斥的，如果之前的记录是isImport，那么需要将之前的删除掉
        String uuid = null;
        CustomerFromContact customerFromContact = null;

        if (o instanceof CustomerFromContact) {
            customerFromContact = (CustomerFromContact) o;
        } else if (o instanceof Contact) {
            Contact contact = (Contact) o;

            customerFromContact = new CustomerFromContact();
            customerFromContact.setUuid(UUID.randomUUID().toString());//
            customerFromContact.setChangeStatus(CustomerConstant.COLUMN_CHANGE_STATUS_ADD);
            customerFromContact.setContactName(contact.name);//
            customerFromContact.setContactRawId(contact.rawContactId);//
            customerFromContact.setContactSortKey(contact.sortKeyString);//

            if (isImport) {

                Customer customer = new Customer();
                int identity = CustomerData.getCurrentUserIdentity();
                String uuid_customer = UUID.randomUUID().toString();
                customer.setUuid(uuid_customer);
                customer.setName(contact.name);
                customer.setNamePrefix(String.valueOf(contact.sortKeyChar));
                customer.setChangeStatus(identity > 0 ? CustomerConstant.COLUMN_CHANGE_STATUS_ADD :
                        CustomerConstant.COLUMN_CHANGE_STATUS_DEFAULT);
                customer.setIdentity(identity);
                customer.setSortKeyString(contact.sortKeyString);
                customer.setSortKeyChar(String.valueOf(contact.sortKeyChar));
                customer.setImportContact("1");
                customer.setCreateTime(new Date());
                customer.setLastActiveTime2(System.currentTimeMillis());
                //添加默认职业类别，1类
                if (customer.getCareerCategory() == null) {
                    customer.setCareerCategory(EnumProfession.ONE.value());
                }
                //添加默认性别，男性
                if (customer.getSex() == null) {
                    customer.setSex(1);
                }
                //添加默认年龄，30
                if (customer.getBirthday() == null) {
                    customer.setBirthday(DateUtils.getDefaultCustomerBirthday());
                }
                //添加默认婚姻状态，未婚
                if (customer.getIsMarrage() == null) {
                    customer.setIsMarrage(0);
                }

                if (customer.getState() == null) {
                    customer.setState(CustomerConstant.COLUMN_CUSTOMER_STATE_IMPORT);
                }

                long ret = insertObject(CustomerConstant.TABLE_CUSTOMER, customer);
                if (ret <= 0) {
                    return null;
                } else {
                    uuid = customer.getUuid();
                    customerFromContact.setCustomerName(contact.name);
                    customerFromContact.setCustomerUuid(uuid_customer);
                }
            } else {
                customerFromContact.setContactIgnore(CustomerConstant.CUSTOMER_GET_IGNORE);
            }
        }
        customerFromContact.setIdentity(CustomerData.getCurrentUserIdentity());
        //首先删除已经存在的，确保与此ignore不同的都删除掉，软删除
        if (!checkAndDeleteCustomerFromContact(customerFromContact)) {
            long ret = insertObject(CustomerConstant.CustomerFromContact.TABLE_NAME, customerFromContact);
            if (ret > 0 && !isImport) {
                uuid = customerFromContact.getUuid();
            }
        } else {
            uuid = customerFromContact.getUuid();
        }
        return uuid;
    }

    /**
     * 检查原先是否已经包含此customer contact
     *
     * @param customerFromContact
     * @return 如果是已经包含，返回true，即不需要进一步插入数据看。没有包含，返回false，需要进一步处理。
     */
    private boolean checkAndDeleteCustomerFromContact(CustomerFromContact customerFromContact) {
        if (customerFromContact != null) {
            Cursor cursor = null;
            try {
                dm.open();
                String sql = "select "
                        + CustomerConstant.CustomerFromContact.UUID + ","
                        + CustomerConstant.CustomerFromContact.CONTACT_IGNORE
                        + " from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                        + " where " + CustomerConstant.CustomerFromContact.CONTACT_NAME + "='" + customerFromContact.contactName
                        + "' and " + CustomerConstant.CustomerFromContact.CONTACT_RAW_ID + "='" + customerFromContact.contactRawId
                        + "' and " + CustomerConstant.CustomerFromContact.IDENTITY + "='" + customerFromContact.identity
                        + "' and " + CustomerConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
                cursor = dm.rawQuery(sql, null);
                if (cursor == null || cursor.getCount() == 0) {
                    return false;
                }
                ArrayList<String> uuids = new ArrayList<>();
                boolean keepFirstFlag = true;
                while (cursor.moveToNext()) {
                    if (keepFirstFlag && CompareHelper.checkIntegerEqual(cursor.isNull(1) ? null : cursor.getInt(1), customerFromContact.contactIgnore)) {
                        keepFirstFlag = false;
                        continue;
                    }
                    uuids.add(cursor.getString(0));
                }
                cursor.close();
                cursor = null;

                for (String uuid : uuids) {
                    dm.delete(CustomerConstant.CustomerFromContact.TABLE_NAME,
                            CustomerConstant.CustomerFromContact.UUID + "='" + uuid + "'", null);
                }
                if (keepFirstFlag) {
                    return false;
                } else {
                    return true;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    if (!cursor.isClosed()) {
                        cursor.close();
                    }
                }
                if (dm != null) {
                    dm.close();
                }
            }
        }
        return false;
    }


    public void removeIgnoredCustomerFromContact(Contact contact) {
        if (contact == null || contact.rawContactId == null) {
            return;
        }
        String whereClause = CustomerConstant.CustomerFromContact.CONTACT_RAW_ID + "='" + contact.rawContactId
                + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY);
        hardDeleteWithWhereClause(CustomerConstant.CustomerFromContact.TABLE_NAME, whereClause);
    }

    /**
     * 更新返回ID
     *
     * @return
     */
    public long updateThresholdTime(Long time) {
        long ret = 0;
        if (time == null) {
            return ret;
        }
        Cursor c = null;
        try {
            dm.open();

            c = dm.rawQuery("select * from " + CustomerConstant.TABLE_CUSTOMER_GET
                    + " where " + CustomerConstant.COLUMN_CUSTOMER_GET_TYPE + "="
                    + CustomerConstant.CUSTOMER_GET_TYPE_THRESHOLD, null);
            if (c == null) {
                return 0;
            }

            ContentValues values = new ContentValues();
            values.put(CustomerConstant.COLUMN_CUSTOMER_GET_THRESHOLD, time);

            if (c.getCount() == 0) {
                values.put(CustomerConstant.COLUMN_CUSTOMER_GET_UUID, UUID.randomUUID().toString());
                values.put(CustomerConstant.COLUMN_CUSTOMER_GET_TYPE, CustomerConstant.CUSTOMER_GET_TYPE_THRESHOLD);
                ret = dm.insert(CustomerConstant.TABLE_CUSTOMER_GET, null, values);
            } else {

                dm.update(CustomerConstant.TABLE_CUSTOMER_GET,
                        values,
                        CustomerConstant.COLUMN_CUSTOMER_GET_TYPE + "="
                                + CustomerConstant.CUSTOMER_GET_TYPE_THRESHOLD, null);
            }

        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (c != null) {
                c.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    //如果使用Contact，那么需要从contact中解析电话号码，需要同时解析电话号码
    public List<Contact> getListOfContactForCustomerImport() {
        List<CustomerFromContact> listIgnores = null;
        List<Contact> listContacts = null;
        List<Contact> listContactsServivors = null;
        Cursor cursor = null;
        try {
            dm.open();
            listContacts = getListOfContact(CustomerConstant.CustomerGet.GET_CONTACT_MAX);

            if (listContacts == null || listContacts.size() == 0) {
                return listContacts;
            }

            String sqlGetIgnores = "select * from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY)
                    + " ORDER BY " + CustomerConstant.CustomerFromContact.CONTACT_SORT_KEY + " ASC;";
            listIgnores = dm.sql2VOList(sqlGetIgnores, new CustomerFromContact());
            //进行比较删除
            Collections.sort(listContacts, CompareHelper.COMPARATOR_CONTACT);
            if (listIgnores == null || listIgnores.size() == 0) {
                listContactsServivors = listContacts;
            } else {
                Collections.sort(listIgnores, CompareHelper.COMPARATOR_CUSTOMER_FROM_CONTACT);
                listIgnores = CompareHelper.removeSameCustomerFromContact(listIgnores);
                listContactsServivors = CompareHelper.setIgnoreMarksOfContactFromCustomerContact(listContacts, listIgnores, false, true);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        if (listContactsServivors != null && listContactsServivors.size() > 0) {
            //获取已经存在的客户名字
            List<String> passedNames = CustomerDBManager
                    .getInstance()
                    .getSimpleCustomerNames(mContext);
            for(Contact item : listContactsServivors){
                if (item.hasImported){
                    if (!passedNames.contains(item.getName())){
                        item.hasImported = false;
                    }
                }
            }
        }
        return listContactsServivors;
    }

    public long getNumberOfCustomerNewInDateRegion(Date dateFrom, Date dateTo) {

        int ret = 0;
        Cursor cursor = null;
        try {
            dm.open();
            String stringFrom = DateUtils.dateTime2String(dateFrom);
            String stringTo = DateUtils.dateTime2String(dateTo);

            String sqlCustomers = "select COUNT(customer.createTime) from " + CustomerConstant.TABLE_CUSTOMER
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + " and " + CustomerConstant.COLUMN_CUSTOMER_CREATE_TIME
                    + " > '" + stringFrom + "'"
                    + " and " + CustomerConstant.COLUMN_CUSTOMER_CREATE_TIME
                    + " < '" + stringTo + "';";
            cursor = dm.rawQuery(sqlCustomers, null);
            if (cursor != null) {
                if (cursor.moveToFirst()) {
                    ret = cursor.getInt(0);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    public static int getCurrentUserIdentity() {
        UserInfo userLicenceInfo = AppContext.getCurrentUser();
        if (userLicenceInfo == null) {
            return 0;
        }
        return userLicenceInfo.getUserId();
    }

    public static boolean isLogin() {
        UserInfo userLicenceInfo = AppContext.getCurrentUser();
        if (userLicenceInfo == null || userLicenceInfo.getUserId() <= 0) {
            return false;
        }
        return true;
    }

    public static String getIdentityWhereClauseString(String columnName) {
        return " and (" + columnName + "='" + getCurrentUserIdentity() + "'"
                + " or " + columnName + "=0 "
                + " or " + columnName + " IS NULL) ";
    }

    public long getNumberOfCustomerProposalInDateRegion(Date dateFrom, Date dateTo) {
        int id = getCurrentUserIdentity();
        if (id == 0) {
            return 0;
        } else {
            QueryBuilder<CustomerInteraction> qb = InteractionUtil.
                    getInstance(mContext.getApplicationContext()).getTopicItemDao().queryBuilder();
            qb.where(qb.or(CustomerInteractionDao.Properties.Category.eq(4),
                    CustomerInteractionDao.Properties.Category.eq(5)),
                    qb.and(CustomerInteractionDao.Properties.UpdateTime.gt(dateFrom.getTime()),
                            CustomerInteractionDao.Properties.UpdateTime.lt(dateTo.getTime()))
                    /*, CustomerInteractionDao.Properties.UserIdentity.eq(id)*/);
            return qb.count();
        }
    }

    public long getNumberOfCustomerIntouchInDateRegion(Date dateFrom, Date dateTo) {

        int id = getCurrentUserIdentity();
        if (id == 0) {
            return 0;
        } else {
            QueryBuilder<CustomerInteraction> qb = InteractionUtil.
                    getInstance(mContext.getApplicationContext()).getTopicItemDao().queryBuilder();
            qb.where(CustomerInteractionDao.Properties.Category.eq(6),
                    qb.and(CustomerInteractionDao.Properties.UpdateTime.gt(dateFrom.getTime()),
                            CustomerInteractionDao.Properties.UpdateTime.lt(dateTo.getTime())),
                    CustomerInteractionDao.Properties.UserIdentity.eq(id));
            return qb.count();
        }
    }

    public List<CustomerModelDetail> getListOfCustomerNewInDateRegion(Date dateFrom, Date dateTo) {
        List<Customer> listCustomers = new ArrayList<>();
        try {
            SQLiteDatabase sqliteDatabase = dm.open();
            String stringFrom = DateUtils.dateTime2String(dateFrom);
            String stringTo = DateUtils.dateTime2String(dateTo);

            String sqlCustomers = "select * from " + CustomerConstant.TABLE_CUSTOMER
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.Customer.IDENTITY)
                    + " and " + CustomerConstant.COLUMN_CUSTOMER_CREATE_TIME
                    + " > '" + stringFrom + "'"
                    + " and " + CustomerConstant.COLUMN_CUSTOMER_CREATE_TIME
                    + " < '" + stringTo + "';";
            listCustomers = getCursorData(sqliteDatabase, sqlCustomers, CustomerModelDetail.TYPE_NEW);
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return CustomerConvertUtil.convertListCustomerToCustomerModelDetail(listCustomers, CustomerModelDetail.TYPE_NEW);
    }

    private List<Customer> getCursorData(SQLiteDatabase db, String sql, int type) {
        if (db == null || TextUtils.isEmpty(sql)) {
            return null;
        }
        Cursor c = db.rawQuery(sql, null);

        if (c != null) {
            List<Customer> listCustomers = new ArrayList<>();
            while (c.moveToNext()) {

                /* 以下是不同类型通用的字段 */

                String UUID = c.getString(c.getColumnIndex("uuid"));
                String name = c.getString(c.getColumnIndex("name"));
                String portraitPath = c.getString(c.getColumnIndex("portraitPath"));

                Customer customer = new Customer();
                customer.setUuid(UUID);
                customer.setName(name);
                customer.setPortraitPath(portraitPath);

                long lastActiveTime2 = 0l;

                switch (type) {
                    case CustomerModelDetail.TYPE_NEW: // 新建客户
                        lastActiveTime2 = c.getLong(c.getColumnIndex("lastActiveTime2"));
                        break;
                    case CustomerModelDetail.TYPE_PROPOSAL: // 新建计划书
                    case CustomerModelDetail.TYPE_INTOUCH: // 查看计划书
                        // 查看或新建计划书都是从轨迹表查找updateTime
                        lastActiveTime2 = c.getLong(c.getColumnIndex("updateTime"));
                        break;
                }

                customer.setLastActiveTime2(lastActiveTime2);
                listCustomers.add(customer);
            }

            c.close();
            c = null;

            return listCustomers;
        }
        return null;
    }

    public List<CustomerModelDetail> getListOfCustomerProposalInDateRegion(Date dateFrom, Date dateTo) {

        int id = getCurrentUserIdentity();
        if (id == 0) {
            return new ArrayList<CustomerModelDetail>();
        }

        List<Customer> listCustomers = null;
        try {
            dm.open();
            String sqlCustomers =
                    "select c.* from "
                            + CustomerConstant.TABLE_CUSTOMER + " c "
                            + "inner join "
                            + CustomerConstant.TABLE_CUSTOMER_INTERACTION + " ci"
                            + " on c.uuid=ci.customerUuid"
                            + " and (ci.category=4 or ci.category=5)"
                            + " and ci.updateTime>" + dateFrom.getTime()
                            + " and ci.updateTime<" + dateTo.getTime()
                            + " and c.ChangeStatus<>" + CustomerConstant.COLUMN_CHANGE_STATUS_DELETE
                            + " group by c.uuid";
            listCustomers = dm.sql2VOList(sqlCustomers, new Customer());
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return CustomerConvertUtil.convertListCustomerToCustomerModelDetail(listCustomers, CustomerModelDetail.TYPE_PROPOSAL);
    }

    public List<CustomerModelDetail> getListOfCustomerProposalInDateRegion2(Date dateFrom, Date dateTo) {

        int id = getCurrentUserIdentity();
        if (id == 0) {
            return new ArrayList<CustomerModelDetail>();
        }

        List<Customer> listCustomers = null;
        try {
            SQLiteDatabase db = dm.open();
            String sqlCustomers =
                    "select c.uuid,c.name,c.portraitPath,ci.updateTime from "
                            + CustomerConstant.TABLE_CUSTOMER + " c "
                            + "inner join "
                            + CustomerConstant.TABLE_CUSTOMER_INTERACTION + " ci"
                            + " on c.uuid=ci.customerUuid"
                            + " and (ci.category=4 or ci.category=5)"
                            + " and ci.UserIdentity=" + id
                            + " and ci.updateTime>" + dateFrom.getTime()
                            + " and ci.updateTime<" + dateTo.getTime()
                            + " and c.ChangeStatus<>" + CustomerConstant.COLUMN_CHANGE_STATUS_DELETE
                            + " group by c.uuid";
            listCustomers = getCursorData(db, sqlCustomers, CustomerModelDetail.TYPE_PROPOSAL);

            if (listCustomers != null) {
                for (Customer c : listCustomers) {
                    if ((c.getLastActiveTime2() == null || c.getLastActiveTime2() == 0) && c.getUpdateTime() != null) {

                        c.setLastActiveTime2(c.getUpdateTime().getTime());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return CustomerConvertUtil.convertListCustomerToCustomerModelDetail(listCustomers, CustomerModelDetail.TYPE_PROPOSAL);
    }

    public List<CustomerModelDetail> getListOfCustomerIntouchInDateRegion(Date dateFrom, Date dateTo) {

        int id = getCurrentUserIdentity();
        if (id == 0) {
            return new ArrayList<CustomerModelDetail>();
        }

        List<Customer> listCustomers = null;
        try {
            SQLiteDatabase db = dm.open();
            String sqlCustomers =
                    "select c.uuid,c.name,c.portraitPath,ci.updateTime  from "
                            + CustomerConstant.TABLE_CUSTOMER + " c "
                            + "inner join "
                            + CustomerConstant.TABLE_CUSTOMER_INTERACTION + " ci"
                            + " on c.uuid=ci.customerUuid"
                            + " and (ci.category=6 or ci.category=8)"
                            + " and ci.UserIdentity=" + id
                            + " and ci.updateTime>" + dateFrom.getTime()
                            + " and ci.updateTime<" + dateTo.getTime()
                            + " and c.ChangeStatus<>" + CustomerConstant.COLUMN_CHANGE_STATUS_DELETE
                            + " group by c.uuid";
            listCustomers = getCursorData(db, sqlCustomers, CustomerModelDetail.TYPE_INTOUCH);
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return CustomerConvertUtil.convertListCustomerToCustomerModelDetail(listCustomers, CustomerModelDetail.TYPE_INTOUCH);
    }

    /**
     * 获取从TABLE_CUSTOMER_FROM_CONTACT的表中存放的数据
     *
     * @return
     */
    public List<CustomerFromContact> getListOfCustomerFromContact() {
        List<CustomerFromContact> list = null;
        try {
            String sql = "select * from " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + getIdentityWhereClauseString(CustomerConstant.CustomerFromContact.IDENTITY)
                    + " ORDER BY " + CustomerConstant.CustomerFromContact.CONTACT_SORT_KEY + " ASC;";
            dm.open();
            list = dm.sql2VOList(sql, new CustomerFromContact());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }

    public List<CustomerSimple> getListOfCustomerSimple() {
        List<CustomerSimple> list = null;
        try {
            String sql = "select * from " + CustomerConstant.CustomerSimple.TABLE_NAME + " ;";
            dm.open();
            list = dm.sql2VOList(sql, new CustomerSimple());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }


    public List<Customer> getListOfCustomersByIdentity(int identity, boolean withDefault) {
        List<Customer> list = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select * from " + CustomerConstant.TABLE_CUSTOMER + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE);

        if (withDefault) {
            if (identity > 0) {
                sb.append(" and (" + DBConstant.TableCustomer.COLUMN_IDENTITY + "='" + identity + "'"
                        + " or " + DBConstant.TableCustomer.COLUMN_IDENTITY + "=0 "
                        + " or " + DBConstant.TableCustomer.COLUMN_IDENTITY + " IS NULL) ");
            } else {
                sb.append(" and (" + DBConstant.TableCustomer.COLUMN_IDENTITY + "=0 "
                        + " or " + DBConstant.TableCustomer.COLUMN_IDENTITY + " IS NULL) ");
            }
        } else {
            if (identity > 0) {
                sb.append(" and " + DBConstant.TableCustomer.COLUMN_IDENTITY + "='" + identity + "' ");
            }
        }

        try {

            String sql = sb.toString() + " ORDER BY "
                    + CustomerConstant.COLUMN_CUSTOMER_SORT_KEY_STRING + " ASC;";
            dm.open();
            list = dm.sql2VOList(sql, new Customer());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }

    public List<Customer> getListOfCustomersByBirthday(String birthday) {
        List<Customer> list = null;
        try {
            String sql = "select * from " + CustomerConstant.TABLE_CUSTOMER
                    + " where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE
                    + " AND  birthday like '%" + birthday + "%'"
                    + " AND  ( state != " + CustomerConstant.COLUMN_CUSTOMER_STATE_IMPORT + " OR state IS NULL ) "
                    + " ORDER BY "
                    + CustomerConstant.COLUMN_CUSTOMER_SORT_KEY_STRING + " ASC;";
            dm.open();
            list = dm.sql2VOList(sql, new Customer());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }

    /**
     * 根据uuid获得客户对象
     *
     * @return
     */
    public Customer getCustomerByUUID(String uuid) {
        Customer c = null;
        try {
            dm.open();
            String sql = "select * from " + DBConstant.TABLE_CUSTOMER + " where uuid = '"
                    + uuid + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            c = dm.sql2VO(sql, new Customer());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return c;
    }

    /**
     * 根据uuid获得客户对象
     *
     * @return
     */
    public Customer getCustomerSimpleByUUID(String uuid) {
        Customer c = null;
        try {
            String TABLE_NAME = "customer_simple";
            String FIELD_CUSTOMER_ID = "customerId";
            dm.open();
            String sql = "select * from " + TABLE_NAME + " where " + FIELD_CUSTOMER_ID + " = '"
                    + uuid + "';";
            c = dm.sql2VO(sql, new Customer());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return c;
    }

    /**
     * 向CustomerSimple表中插入一条数据
     *
     * @param c
     */
    public void insertCustomerToSimple(CustomerDetail c) {
        if (c == null) {
            return;
        }
        String TABLE_NAME = "customer_simple";
        String FIELD_CUSTOMER_ID = "customerId";
        String FIELD_NAME = "name";
        String FIELD_SEX = "sex";
        String FIELD_TAG = "tag";
        String FIELD_NAME_PREFIX = "namePrefix";

        ContentValues contentValues = new ContentValues();
        contentValues.put(FIELD_CUSTOMER_ID, c.getCustomerId());
        contentValues.put(FIELD_NAME, c.getName());
        contentValues.put(FIELD_SEX, c.getSex());
        contentValues.put(FIELD_TAG, c.getTag());
        contentValues.put(FIELD_NAME_PREFIX, c.getNamePrefix());

        dm.open();
        dm.beginTransaction();
        dm.insert(TABLE_NAME, "", contentValues);
        dm.setTransactionSuccessful();
        dm.endTransaction();
        dm.close();
    }

    /**
     * 向CustomerSimple表中插入一条数据
     *
     * @param c
     */
    public void updateCustomerToSimple(CustomerDetail c) {
        if (c == null) {
            return;
        }
        String TABLE_NAME = "customer_simple";
        String FIELD_CUSTOMER_ID = "customerId";
        String FIELD_NAME = "name";
        String FIELD_SEX = "sex";
        String FIELD_TAG = "tag";
        String FIELD_NAME_PREFIX = "namePrefix";

        ContentValues contentValues = new ContentValues();
        contentValues.put(FIELD_CUSTOMER_ID, c.getCustomerId());
        contentValues.put(FIELD_NAME, c.getName());
        contentValues.put(FIELD_SEX, c.getSex());
        contentValues.put(FIELD_TAG, c.getTag());
        contentValues.put(FIELD_NAME_PREFIX, c.getNamePrefix());

        dm.open();
        dm.beginTransaction();
        dm.update(TABLE_NAME, contentValues, FIELD_CUSTOMER_ID + "=?", new String[]{c.getCustomerId()});
        dm.setTransactionSuccessful();
        dm.endTransaction();
        dm.close();
    }

    /**
     * 删除CustomerSimple表中指定uuid的记录
     *
     * @param uuid
     */
    public void deleteCustomerIfExists(String uuid) {
        if (null == uuid || "".equals(uuid)) {
            return;
        }
        String TABLE_NAME = "customer_simple";
        String FIELD_CUSTOMER_ID = "customerId";

        dm.open();
        dm.delete(TABLE_NAME, FIELD_CUSTOMER_ID + "=?", new String[]{uuid});
        dm.close();
    }


    public void deleteCustomerFromContact(List<String> uuids){
        if (uuids != null && uuids.size() > 0) {
            StringBuffer sql = new StringBuffer();
            sql.append("DELETE FROM " + CustomerConstant.CustomerFromContact.TABLE_NAME
                    + " WHERE " + CustomerConstant.CustomerFromContact.CUSTOMER_UUID + "=?;");
            DatabaseManager dbManager = DatabaseManager.getInstance(mContext);
            dbManager.open();
            SQLiteDatabase db = dbManager.getSqliteDatabase();
            SQLiteStatement statement = db.compileStatement(sql.toString());
            db.beginTransaction();

            try {
                int size = uuids.size();
                for (int i = 0; i < size; i++) {
                    statement.bindString(1, uuids.get(i));
                    statement.executeUpdateDelete();
                }
                db.setTransactionSuccessful();
            } catch (Exception e) {
                e.printStackTrace();
//                Toast.makeText(mContext, "联系人导入失败", Toast.LENGTH_SHORT).show();
            } finally {
                db.endTransaction();
            }
            dbManager.close();
        } else {
//            Toast.makeText(mContext, "未导入联系人", Toast.LENGTH_SHORT).show();
        }
    }

    /**
     * 客户是否存在customer_simple
     *
     * @param uuid
     * @return
     */
    public boolean isCustomerExisting(String uuid) {
        if (null == uuid || 0 == uuid.length()) {
            return false;
        }
        String TABLE_NAME = "customer_simple";
        String FIELD_CUSTOMER_ID = "customerId";

        dm.open();
        Cursor c = dm.query(TABLE_NAME, new String[]{FIELD_CUSTOMER_ID}, FIELD_CUSTOMER_ID + "=?", new String[]{uuid});
        dm.close();
        if (c != null && c.getCount() > 0) {
            return true;
        }
        c.close();
        return false;
    }

    /**
     * 客户是否存在
     *
     * @param uuid
     * @return
     */
    public boolean checkCustomerExist(String uuid) {
        if (uuid == null || uuid.trim().length() == 0) {
            return false;
        }

        long count = 0;
        try {
            dm.open();
            String sql = "select count(*) from " + DBConstant.TABLE_CUSTOMER + " where uuid = '"
                    + uuid + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            count = dm.getCount(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return count > 0;
    }

    /**
     * 根据uuid假删除一个客户
     */
    public long pretendDeleteACustomerByUuid(String uuid) {
        long ret = 0;
        try {
            dm.open();

            ret += softDelete(DBConstant.TABLE_CUSTOMER, uuid);
            ret += hardDelete(DBConstant.TABLE_CUSTOMER, uuid);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 根据uuid假删除一个客户
     */
    public long pretendDeleteByUuid(String tableName, String uuid) {
        long ret = 0;
        try {
            dm.open();
            ret += softDelete(tableName, uuid);
            ret += hardDelete(tableName, uuid);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 根据uuid假删除一个客户
     */
    public long pretendDeleteByColumeNameAndValue(String tableName, String columnName,
                                                  String columnValue) {
        long ret = 0;
        try {
            dm.open();
            ret += softDelete(tableName, columnName, columnValue);
            ret += hardDelete(tableName, columnName, columnValue);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    //======contact======
    public List<ACustomerInfoItemContent> getCustomerInfoItemContactContentByCustomerUuid(String customerUUId) {
        List<ACustomerInfoItemContent> mListItemContent = new ArrayList<ACustomerInfoItemContent>();
        try {
            dm.open();

            List<CustomerInfoItemContactContent> list = new ArrayList<CustomerInfoItemContactContent>();
            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_CONTACT
                    + " where customerUUId='" + customerUUId + "' and customer_contact.ctype='1' "
                    + " and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY customer_contact.isDefault DESC";
            list = dm.sql2VOList(sql, new CustomerInfoItemContactContent());
            if (list != null) {
                mListItemContent.addAll(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return mListItemContent;
    }

    /**
     * 删除表customer_contact中的一条信息
     */
    public long pretendDeleteAContact(String uuid) {
        long ret = 0;
        try {
            dm.open();
            ret += softDelete(DBConstant.TABLE_CUSTOMER_CONTACT, uuid);
            ret += hardDelete(DBConstant.TABLE_CUSTOMER_CONTACT, uuid);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    //=============email================
    public List<ACustomerInfoItemContent> getCustomerInfoItemEmailContentByCustomerID(String customerUUId) {
        List<ACustomerInfoItemContent> mListItemContent = new ArrayList<ACustomerInfoItemContent>();
        try {
            dm.open();

            List<CustomerInfoItemEmailContent> list = new ArrayList<CustomerInfoItemEmailContent>();
            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_CONTACT
                    + " where customerUUId='" + customerUUId + "' and customer_contact.ctype='2' "
                    + " and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY customer_contact.isDefault DESC";
            list = dm.sql2VOList(sql, new CustomerInfoItemEmailContent());
            if (list != null) {
                mListItemContent.addAll(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return mListItemContent;
    }

    //=============IM==============
    public List<ACustomerInfoItemContent> getCustomerInfoItemIMContentByCustomerID(String customerUUId) {
        List<ACustomerInfoItemContent> mListItemContent = new ArrayList<ACustomerInfoItemContent>();
        try {
            dm.open();

            List<CustomerInfoItemIMContent> list = new ArrayList<CustomerInfoItemIMContent>();
            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_CONTACT
                    + " where customerUUId='" + customerUUId + "' and customer_contact.ctype='3' "
                    + " and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY customer_contact.isDefault DESC";
            list = dm.sql2VOList(sql, new CustomerInfoItemIMContent());
            if (list != null) {
                mListItemContent.addAll(list);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return mListItemContent;
    }

    //============note===============
    public List<EntityCustomerNote> getListOfNoteByCustomerUuid(String customerUUId) {
        List<EntityCustomerNote> list = null;
        try {
            dm.open();

            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_NOTE
                    + " where customerUUId='" + customerUUId
                    + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY "
                    + CustomerConstant.COLUMN_NOTE_LAST_UPDATE_TIME + " DESC";
            list = dm.sql2VOList(sql, new EntityCustomerNote());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }

    public long insertEntityCustomerNote(EntityCustomerNote entity) {
        if (entity == null) return 0;
        long rowId = 0;
        try {
            dm.open();
            ContentValues cv = new ContentValues();
            if (entity.uuid != null) {
                cv.put("uuid", entity.uuid);
            }
            if (entity.customerUuid != null) {
                cv.put("customerUuid", entity.customerUuid);
            }
            if (entity.description != null) {
                cv.put("description", entity.description);
            }
            if (entity.typeUuid != null) {
                cv.put("typeUuid", entity.typeUuid);
            }
            if (entity.createTime != null) {
                cv.put("createTime", entity.createTime);
            }
            if (entity.lastUpdateTime != null) {
                cv.put("lastUpdateTime", DateUtils.dateTime2String(entity.lastUpdateTime, DateUtils.YYYY_MM_DD_HH_MM_SS));
            }
            if (entity.lastActiveTime2 != null) {
                cv.put("lastActiveTime2", entity.lastActiveTime2);
            }
            if (entity.ChangeStatus != null) {
                cv.put("ChangeStatus", entity.ChangeStatus);
            }
            rowId = dm.insert(CustomerConstant.CustomerNote.TABLE_NAME, null, cv);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return rowId;
    }

    /**
     * 更新返回ID
     *
     * @return
     */
    public int updateEntityCustomerNoteWithChangeStatus(EntityCustomerNote entity) {
        if (entity == null || entity.uuid == null) {
            return 0;
        }
        int ret = 0;
        try {
            ContentValues cv = new ContentValues();
            if (entity.uuid != null) {
                cv.put("uuid", entity.uuid);
            }
            if (entity.customerUuid != null) {
                cv.put("customerUuid", entity.customerUuid);
            }
            if (entity.description != null) {
                cv.put("description", entity.description);
            }
            if (entity.typeUuid != null) {
                cv.put("typeUuid", entity.typeUuid);
            }
            if (entity.createTime != null) {
                cv.put("createTime", entity.createTime);
            }
            if (entity.lastUpdateTime != null) {
                cv.put("lastUpdateTime", DateUtils.dateTime2String(entity.lastUpdateTime, DateUtils.YYYY_MM_DD_HH_MM_SS));
            }
            if (entity.lastActiveTime2 != null) {
                cv.put("lastActiveTime2", entity.lastActiveTime2);
            }
            if (entity.ChangeStatus != null) {
                cv.put("ChangeStatus", entity.ChangeStatus);
            }
            dm.open();
            String sql = "select " + CustomerConstant.CustomerNote.UUID + "," + CustomerConstant.CustomerNote.CHANGE_STATUES
                    + " from " + CustomerConstant.CustomerNote.TABLE_NAME
                    + " where " + CustomerConstant.CustomerNote.UUID + "='" + entity.uuid + "';";
            Cursor cursor = dm.rawQuery(sql, null);
            if (cursor == null) {
                if (dm.insert(CustomerConstant.CustomerNote.TABLE_NAME, null, cv) > 0) {
                    ret++;
                }
            } else {
                if (cursor.moveToFirst()) {
                    int oldChangeStatus = cursor.getInt(cursor.getColumnIndex(CustomerConstant.CustomerNote.CHANGE_STATUES));
                    Integer newChangeStatus = entity.ChangeStatus;
                    if (newChangeStatus == null || newChangeStatus == oldChangeStatus) {
                        dm.delete(CustomerConstant.CustomerNote.TABLE_NAME,
                                CustomerConstant.CustomerNote.UUID + "='" + entity.uuid + "'", null);
                        if (dm.insert(CustomerConstant.CustomerNote.TABLE_NAME, null, cv) > 0) {
                            ret++;
                        }
                    } else {
                        if (oldChangeStatus == CustomerConstant.COLUMN_CHANGE_STATUS_ADD
                                && newChangeStatus == CustomerConstant.COLUMN_CHANGE_STATUS_DELETE) {
                            dm.delete(CustomerConstant.CustomerNote.TABLE_NAME,
                                    CustomerConstant.CustomerNote.UUID + "='" + entity.uuid + "'", null);
                        } else {
                            dm.delete(CustomerConstant.CustomerNote.TABLE_NAME,
                                    CustomerConstant.CustomerNote.UUID + "='" + entity.uuid + "'", null);
                            if (dm.insert(CustomerConstant.CustomerNote.TABLE_NAME, null, cv) > 0) {
                                ret++;
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            dm.close();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    private String getNotNullString(String s) {
        return s == null ? "" : s;
    }

    private Long getNotNullLong(Long l) {
        return l == null ? 0 : l;
    }

    public EntityCustomerNote getNoteByUuid(String uuid) {
        EntityCustomerNote entity = null;
        try {
            dm.open();

            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_NOTE
                    + " where uuid='" + uuid
                    + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            entity = dm.sql2VO(sql, new EntityCustomerNote());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return entity;
    }

    //============attachment===============
    public List<EntityCustomerAttachment> getListOfAttachmentByCustomerUuid(String customerUUId) {
        List<EntityCustomerAttachment> list = null;
        try {
            dm.open();

            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_ATTACHMENT
                    + " where customerUUId='" + customerUUId
                    + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY "
                    + CustomerConstant.COLUMN_CUSTOMER_ATTACHMENT_TIME + " DESC";
            list = dm.sql2VOList(sql, new EntityCustomerAttachment());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return list;
    }

    public EntityCustomerAttachment getAttachmentByUuid(String uuid) {
        EntityCustomerAttachment entity = null;
        try {
            dm.open();

            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_ATTACHMENT
                    + " where uuid='" + uuid
                    + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            entity = dm.sql2VO(sql, new EntityCustomerAttachment());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return entity;
    }


    /**
     * 删除表customer_attachment中的一条信息
     */
    public long pretendDeleteAnAttachment(String uuid) {
        long ret = 0;
        try {
            dm.open();

            ret += softDelete(DBConstant.TABLE_CUSTOMER_ATTACHMENT, uuid);
            ret += hardDelete(DBConstant.TABLE_CUSTOMER_ATTACHMENT, uuid);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 新增一个附件
     */
    public String insertEntityCustomerAttachment(EntityCustomerAttachment itemAttachment, String customerUUID) {

        itemAttachment.customerUUId = customerUUID;

        if (TextUtils.isEmpty(itemAttachment.uuid)) {
            itemAttachment.uuid = UUID.randomUUID().toString();
        }
        itemAttachment.ChangeStatus = DBConstant.COLUMN_CHANGE_STATUS_ADD;

        long retCount = -1;
        try {
            dm.open();
            retCount = dm.insert(DBConstant.TABLE_CUSTOMER_ATTACHMENT, null, itemAttachment);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        if (retCount > 0) {
            return itemAttachment.uuid;
        } else {
            return null;
        }
    }

    /**
     * 根据uuid假删除一个地址
     */
    public long pretendDeleteAnAddress(String uuid) {
        long ret = 0;
        try {
            dm.open();

            ret += softDelete(DBConstant.TABLE_CUSTOMER_ADDRESS, uuid);
            ret += hardDelete(DBConstant.TABLE_CUSTOMER_ADDRESS, uuid);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    public List<ACustomerInfoItemContent> getCustomerInfoItemAddressContentByCustomerUuid(String customerUUId) {
        List<ACustomerInfoItemContent> mListItemContent = new ArrayList<ACustomerInfoItemContent>();
        try {
            dm.open();
            List<EntityCustomerAddress> list = new ArrayList<EntityCustomerAddress>();
            String sql = "select * from " + DBConstant.TABLE_CUSTOMER_ADDRESS
                    + " where customerUUId='" + customerUUId
                    + "' and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            list = dm.sql2VOList(sql, new EntityCustomerAddress());
            if (list != null) {
                mListItemContent.addAll(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return mListItemContent;
    }

    /**
     * 获取默认的演示对象
     *
     * @return
     */
    public Customer getDefaultDemonstrate() {
        Customer customer = null;
        try {
            String sql = "select * from customer where isTopDemo = 1 and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            dm.open();
            customer = dm.sql2VO(sql, new Customer());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return customer;
    }

    public Customer setADefaultCustomer() {
        try {
            dm.open();
            List<Customer> list = new ArrayList<Customer>();
            String sql1 = "select * from " + DBConstant.TABLE_CUSTOMER + " where relationFlag=1 and " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE + " ORDER BY lower(namePrefix) asc limit 1";
            list = dm.sql2VOList(sql1, new Customer());

            if (list.size() > 0) {
                Customer c = list.get(0);

                if (c != null && !TextUtils.isEmpty(c.getUuid())) {
                    ContentValues cv = new ContentValues();
                    cv.put("isTopDemo", 1);
                    int ret = dm.updateWithChangeStatus(DBConstant.TABLE_CUSTOMER, new String[]{"uuid"}, new String[]{c.getUuid()}, cv);
                    if (ret > 0) {
                        c.setIsTopDemo(1);
                        return c;
                    } else {
                        return null;
                    }
                }
            } else {
                // 新增一个customer
                Customer c = new Customer();
                c.setUuid(UUID.randomUUID().toString());
                c.setIsRegular(1);
                // 由于关系人模块不支持选择已经存在的customer，所以这里的status只可能是add
                c.setChangeStatus(DBConstant.COLUMN_CHANGE_STATUS_ADD);
                c.setIsDemo(0);
                c.setName("帅哥");
                c.setCareerCategory(1);
                c.setSex(1);
                c.setBirthday(DateUtils.getBirthday(30));
                c.setIsTopDemo(1);

                return c;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return null;
    }

    //limit为获取的contact的个数的最大值
    public List<Contact> getListOfContact(int limit) {
        List<Contact> list = new ArrayList<Contact>();
        Cursor cursor = null;
        try {
            Uri uri = ContactsContract.Contacts.CONTENT_URI;
            cursor = mContext.getContentResolver().query(
                    uri,
                    new String[]{
                            ContactsContract.Contacts.DISPLAY_NAME,
                            CustomerConstant.CustomerImport.NAME_RAW_CONTACT_ID
                    },
                    null,
                    null,
                    CustomerConstant.CustomerImport.NAME_RAW_CONTACT_ID + " desc");

            if (cursor != null) {
                int count = 0;
                while (count < limit && cursor.moveToNext()) {
                    list.add(new Contact(cursor.getString(0), cursor.getString(1), false));
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;
    }

    /**
     * 根据uuid获取customer_groups中的某一个group
     *
     * @return
     */
    public EntityCustomerGroups getGroupByGroupUuid(String groupUuid) {
        EntityCustomerGroups cg = null;
        if (!TextUtils.isEmpty(groupUuid)) {
            try {
                String sql = "select * from customer_groups where uuid ='" + groupUuid + "' and "
                        + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
                dm.open();
                cg = dm.sql2VO(sql, new EntityCustomerGroups());
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (dm != null) {
                    dm.close();
                }
            }
        }
        return cg;
    }

    /**
     * 获取customer_group 表中所有的item
     *
     * @return
     */
    public ArrayList<EntityCustomerGroups> getAllGroups() {
        ArrayList<EntityCustomerGroups> ccgs = null;
        try {
            String sql = "select * from customer_groups where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            dm.open();
            ccgs = (ArrayList<EntityCustomerGroups>) dm.sql2VOList(sql, new EntityCustomerGroups());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ccgs;
    }

    /**
     * 获取customer所在的群组uuid
     *
     * @return
     */
    public long pretendDeleteGroupByUuid(String groupUuid) {
        long ret = 0;
        try {
            dm.open();

            ret += hardDelete(DBConstant.TABLE_CUSTOMER_GROUPS, groupUuid);
            ret += softDelete(DBConstant.TABLE_CUSTOMER_GROUPS, groupUuid);

            ret += softDeleteWithExtraCondition(DBConstant.TABLE_CUSTOMER_C_GROUP, "groupUuid", groupUuid);
            ret += softDeleteWithExtraCondition(DBConstant.TABLE_CUSTOMER_C_GROUP, "groupUuid", groupUuid);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 获取customer所在的群组uuid
     *
     * @return
     */
    public EntityCustomerCGroup getGroupUuidByCustomerUuid(String customerUuid) {
        EntityCustomerCGroup ccg = null;
        try {
            String sql = "select * from customer_c_group where customerUUId ='" + customerUuid + "' and "
                    + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            dm.open();
            ccg = dm.sql2VO(sql, new EntityCustomerCGroup());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ccg;
    }

    public String getGroupUuidByName(String name) {

        String ret = null;
        try {
            String sql = "select customer_groups.uuid from customer_groups where name='" + name + "' and "
                    + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            dm.open();
            Cursor c = dm.rawQuery(sql, null);
            if (c != null && c.getCount() > 0) {
                c.moveToFirst();
                ret = c.getString(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return ret;
    }

    /**
     * 根据uuid获取customer_groups中的某一个group
     *
     * @return
     */
    public void initCustomerGroups() {
        try {
            String sql = "select count(*) from customer_groups where " + DBConstant.COLUMN_CHANGE_STATUS_NOT_DELETE;
            dm.open();
            Cursor c = dm.rawQuery(sql, null);

            if (c != null && c.getCount() > 0) {
                c.moveToFirst();
                int countRows = c.getInt(0);
                if (countRows == 0) {
                    String[] items = mContext.getResources().getStringArray(R.array.customer_group_types);
                    for (String item : items) {
                        EntityCustomerGroups ecg = new EntityCustomerGroups();
                        ecg.setUuid(UUID.randomUUID().toString());
                        ecg.setName(item);
                        ecg.setRemark("1");
                        ecg.setChangeStatus(DBConstant.COLUMN_CHANGE_STATUS_DEFAULT);
                        dm.insert(DBConstant.TABLE_CUSTOMER_GROUPS, null, ecg);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
    }

    /**
     * 新创建一个自定义的群
     *
     * @return
     */
    public String addANewGroup(String name) {
        String uuid = null;
        try {
            dm.open();

            EntityCustomerGroups ecg = new EntityCustomerGroups();
            uuid = UUID.randomUUID().toString();
            ecg.setUuid(uuid);
            ecg.setName(name);
            ecg.setChangeStatus(DBConstant.COLUMN_CHANGE_STATUS_ADD);

            dm.insert(DBConstant.TABLE_CUSTOMER_GROUPS, null, ecg);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dm != null) {
                dm.close();
            }
        }
        return uuid;
    }

    /**
     * 软删除某一条数据，将changestatus标志位置为delete。依据是，原changestatus状态不为add
     */
    public int softDelete(String tableName, String uuid) {
        return dm.updateColumnByWhereClause(tableName,
                DBConstant.COLUMN_CHANGE_STATUS,
                DBConstant.COLUMN_CHANGE_STATUS_DELETE,
                "uuid='" + uuid + "' and " + DBConstant.COLUMN_CHANGE_STATUS + "<>" + DBConstant.COLUMN_CHANGE_STATUS_ADD);
    }

    /**
     * 硬删除某一条数据，将某条数据从数据库中删除。依据是，原changestatus状态为add
     */
    public int hardDelete(String tableName, String uuid) {
        int ret = 0;
        if (CustomerConstant.Customer.TABLE_NAME.equals(tableName)) {
            ret += dm.delete(tableName,
                    "uuid='" + uuid + "' and ("
                            + CustomerConstant.Customer.IDENTITY
                            + " is null or "
                            + CustomerConstant.Customer.IDENTITY
                            + "=0);",
                    null);
        }
        ret += dm.delete(tableName,
                "uuid='" + uuid + "' and "
                        + DBConstant.COLUMN_CHANGE_STATUS
                        + "=" + DBConstant.COLUMN_CHANGE_STATUS_ADD,
                null);

        return ret;
    }

    /**
     * 软删除某一条数据，将changestatus标志位置为delete。依据是，原changestatus状态不为add
     */
    public int softDelete(String tableName, String columnName, String columnValue) {
        return dm.updateColumnByWhereClause(tableName,
                DBConstant.COLUMN_CHANGE_STATUS,
                DBConstant.COLUMN_CHANGE_STATUS_DELETE,
                columnName + "='" + columnValue + "' and " + DBConstant.COLUMN_CHANGE_STATUS + "<>" + DBConstant.COLUMN_CHANGE_STATUS_ADD);
    }

    /**
     * 硬删除某一条数据，将某条数据从数据库中删除。依据是，原changestatus状态为add
     */
    public int hardDelete(String tableName, String columnName, String columnValue) {
        return dm.delete(tableName,
                columnName + "='" + columnValue + "' and "
                        + DBConstant.COLUMN_CHANGE_STATUS
                        + "=" + DBConstant.COLUMN_CHANGE_STATUS_ADD,
                null);
    }

    public int hardDeleteWithWhereClause(String tableName, String whereClause) {
        return dm.delete(tableName,
                whereClause,
                null);
    }

    /**
     * 软删除某一条数据，将changestatus标志位置为delete。依据是，原changestatus状态不为add
     */
    public int softDeleteWithExtraCondition(String tableName, String columnName, String columnValue) {
        return dm.updateColumnByWhereClause(tableName,
                DBConstant.COLUMN_CHANGE_STATUS,
                DBConstant.COLUMN_CHANGE_STATUS_DELETE,
                columnName + "='" + columnValue + "' and " + DBConstant.COLUMN_CHANGE_STATUS + "<>" + DBConstant.COLUMN_CHANGE_STATUS_ADD);
    }

    /**
     * 硬删除某一条数据，将某条数据从数据库中删除。依据是，原changestatus状态为add
     */
    public int hardDeleteWithExtraCondition(String tableName, String columnName, String columnValue) {
        return dm.delete(tableName,
                columnName + "='" + columnValue + "' and "
                        + DBConstant.COLUMN_CHANGE_STATUS
                        + "=" + DBConstant.COLUMN_CHANGE_STATUS_ADD,
                null);
    }

}
